package com.junzhiit.nengji.shop.dao.impl;

import java.util.Date;
import java.util.List;

import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Repository;

import com.bainaeco.framework.base.dao.AbstractBaseDAO;
import com.bainaeco.framework.common.jdbc.StatementParameter;
import com.bainaeco.framework.enums.IsDelete;
import com.junzhiit.nengji.shop.bo.AnalysisBO;
import com.junzhiit.nengji.shop.bo.HomeTableBO;
import com.junzhiit.nengji.shop.bo.TransactionAnalysisBO;
import com.junzhiit.nengji.shop.dao.TransactionDAO;
import com.junzhiit.nengji.shop.model.Transaction;
import com.junzhiit.nengji.shop.model.enums.AnalysisType;
import com.junzhiit.nengji.shop.model.enums.Is;
import com.junzhiit.nengji.shop.model.enums.ShopHistoryType;
import com.junzhiit.nengji.shop.model.enums.TransactionAnalysisStatus;

@Repository
public class TransactionDAOImpl extends AbstractBaseDAO<Transaction> implements TransactionDAO {

	@Override
	public TransactionAnalysisBO getTransactionAnalysis(String shopId,Integer analysisType,String date) {
		StringBuffer sql = new StringBuffer();
		StatementParameter param = new StatementParameter();

		sql.append("select o.orders_user_count,o.orders_count,o.orders_goods_count,");
		sql.append("o.orders_money,t.pay_user_count,t.pay_orders_count,");
		sql.append("t.pay_money,t.pay_number,t.client_price,o.orders_user_count/s.vu 'orders_conversion', ");
		sql.append("t.pay_user_count/s.vu 'pay_conversion',t.pay_user_count/o.orders_user_count 'pay_success'");
		
		if(analysisType==AnalysisType.DAY.toIntValue()){
			sql.append(",(SELECT count(*) from (SELECT count(id) c from transaction where is_delete = ? and shop_id = ? and");
			sql.append(" date_format(created_time ,'%Y-%m-%d')>=date_sub(?, interval 7 day)");
			sql.append(" GROUP BY user_id HAVING c>=2)q) 'seven_repeat_purchase',");
			param.setInt(IsDelete.NO.toIntValue());
			param.setString(shopId);
			param.setString(date);
			
			sql.append("(SELECT count(*) from (SELECT count(id) c from transaction where is_delete = ? and shop_id = ? and");
			sql.append(" date_format(created_time ,'%Y-%m-%d')>=date_sub(?, interval 30 day)");
			sql.append(" GROUP BY user_id HAVING c>=2)w) 'thirty_repeat_purchase',");
			param.setInt(IsDelete.NO.toIntValue());
			param.setString(shopId);
			param.setString(date);
			
			sql.append("(SELECT count(*) from (SELECT count(id) c from transaction where is_delete = ? and shop_id = ? and");
			sql.append(" date_format(created_time ,'%Y-%m-%d')>=date_sub(?, interval 90 day)");
			sql.append(" GROUP BY user_id HAVING c>=2)e) 'ninety_repeat_purchase'");
			param.setInt(IsDelete.NO.toIntValue());
			param.setString(shopId);
			param.setString(date);
		}
		
		sql.append(" from (select count(distinct os.user_id) 'orders_user_count',");
		sql.append("count(os.id) 'orders_count',sum(os.pay_money) 'orders_money',sum(og.number) 'orders_goods_count'");
		sql.append(" from `orders` os inner join orders_goods og on os.id=og.order_id where os.created_time like ?");
		
		param.setString("%" + date + "%");
		if(StringUtils.isNotBlank(shopId)){
			sql.append(" and shop_id=?");
			param.setString(shopId);
		}
		
		sql.append(") o,(select count(distinct user_id) 'pay_user_count',");
		sql.append("count(*) 'pay_orders_count',sum(pay_money) 'pay_money',");
		sql.append("sum(number) 'pay_number',sum(pay_money)/count(distinct user_id) 'client_price'");
		sql.append(" from `transaction` where created_time like ?");
		
		param.setString("%" + date + "%");
		if(StringUtils.isNotBlank(shopId)){
			sql.append(" and shop_id=?");
			param.setString(shopId);
		}
		sql.append(") t,(select count(distinct user_id) 'vu'");
		sql.append(" from shop_history where is_delete=?");
		sql.append(" and shop_id=? and type=? and created_time like ?) s");
		param.setInt(IsDelete.NO.toIntValue());
		param.setString(shopId);
		param.setInt(ShopHistoryType.VIEW.toIntValue());
		param.setString("%"+date+"%");
		
		return query(sql.toString(), TransactionAnalysisBO.class, param);
	}
	
	@Override
	public TransactionAnalysisBO getTransactionAnalysis(String shopId, Date startTime, Date endTime) {
		StringBuffer sql = new StringBuffer();
		StatementParameter param = new StatementParameter();

		sql.append("select o.orders_user_count,o.orders_count,o.orders_goods_count,");
		sql.append("o.orders_money,t.pay_user_count,t.pay_orders_count,");
		sql.append("t.pay_money,t.pay_number,t.client_price,o.orders_user_count/s.vu 'orders_conversion', ");
		sql.append("t.pay_user_count/s.vu 'pay_conversion',t.pay_user_count/o.orders_user_count 'pay_success'");
		
		sql.append(" from (select count(distinct os.user_id) 'orders_user_count',");
		sql.append("count(os.id) 'orders_count',sum(os.pay_money) 'orders_money',sum(og.number) 'orders_goods_count'");
		sql.append(" from `orders` os inner join orders_goods og on os.id=og.order_id where os.is_delete=?");
		sql.append(" and os.created_time >= ? and os.created_time <= ?");
		param.setInt(IsDelete.NO.toIntValue());
		param.setDate(startTime);
		param.setDate(endTime);
		
		if(StringUtils.isNotBlank(shopId)){
			sql.append(" and shop_id=?");
			param.setString(shopId);
		}
		
		sql.append(") o,(select count(distinct user_id) 'pay_user_count',");
		sql.append("count(*) 'pay_orders_count',sum(pay_money) 'pay_money',");
		sql.append("sum(number) 'pay_number',sum(pay_money)/count(distinct user_id) 'client_price'");
		sql.append(" from `transaction` where created_time >= ? and created_time <= ?");
		param.setDate(startTime);
		param.setDate(endTime);
		
		if(StringUtils.isNotBlank(shopId)){
			sql.append(" and shop_id=?");
			param.setString(shopId);
		}
		sql.append(") t,(select count(distinct user_id) 'vu'");
		sql.append(" from shop_history where is_delete=?");
		sql.append(" and shop_id=? and type=? and created_time >= ? and created_time <= ?) s");
		param.setInt(IsDelete.NO.toIntValue());
		param.setString(shopId);
		param.setInt(ShopHistoryType.VIEW.toIntValue());
		param.setDate(startTime);
		param.setDate(endTime);
		
		return query(sql.toString(), TransactionAnalysisBO.class, param);
	}

	@Override
	public List<AnalysisBO> findAllTransactionAnalysis(String shopId,Integer status,Integer analysisType, Date date) {
		StringBuffer sql = new StringBuffer();
		StatementParameter param = new StatementParameter();
		
		sql.append("select");
		sql.append(" date_format(created_time ,?) date,");
		
		if(analysisType==AnalysisType.DAY.toIntValue()||analysisType==AnalysisType.CUSTOM.toIntValue()){
			param.setString("%Y-%m-%d");
		}else if(analysisType==AnalysisType.MONTH.toIntValue()){
			param.setString("%Y-%m");
		}
		
		if(status==TransactionAnalysisStatus.ORDERSCOUNT.toIntValue()){
			sql.append("count(*) value from `orders` where 1=1");
		}
		if(status==TransactionAnalysisStatus.PAYMONEY.toIntValue()){
			sql.append("sum(pay_money) value from `transaction` where 1=1");
		}
		if(status==TransactionAnalysisStatus.ORDERSUSERCOUNT.toIntValue()){
			sql.append("count(distinct user_id) value from `orders` where 1=1");
		}
		if(status==TransactionAnalysisStatus.ORDERSMONEY.toIntValue()){
			sql.append("sum(pay_money) value from `orders` where 1=1");
		}
		if(status==TransactionAnalysisStatus.PAYUSERCOUNT.toIntValue()){
			sql.append("count(distinct user_id) value from `transaction` where 1=1");
		}
		if(status==TransactionAnalysisStatus.PAYORDERSCOUNT.toIntValue()){
			sql.append("count(*) value from `transaction` where 1=1");
		}
		
		if(StringUtils.isNotBlank(shopId)){
			sql.append(" and shop_id = ?");
			param.setString(shopId);
		}
		
		sql.append(" group by date");
		if(analysisType==AnalysisType.DAY.toIntValue()){
			sql.append(" having date <= date_format(date_sub(?, interval ? day),?)");
			sql.append(" and date >= date_format(date_sub(?, interval ? day),?)");
			param.setDate(date);
			param.setInt(0);
			param.setString("%Y-%m-%d");
			param.setDate(date);
			param.setInt(29);
			param.setString("%Y-%m-%d");
		}else if(analysisType==AnalysisType.MONTH.toIntValue()){
			sql.append(" having date <= date_format(date_sub(?, interval ? month),?)");
			sql.append(" and date >= date_format(date_sub(?, interval ? month),?)");
			param.setDate(date);
			param.setInt(0);
			param.setString("%Y-%m");
			param.setDate(date);
			param.setInt(5);
			param.setString("%Y-%m");
		}
		return queryForList(sql.toString(), AnalysisBO.class, param);
	}
	
	@Override
	public List<AnalysisBO> findAllTransactionAnalysis(String shopId, Integer status,
			Date startTime, Date endTime) {
		StringBuffer sql = new StringBuffer();
		StatementParameter param = new StatementParameter();
		
		sql.append("select");
		sql.append(" date_format(created_time ,?) date,");
		
		param.setString("%Y-%m-%d");
		
		if(status==TransactionAnalysisStatus.ORDERSCOUNT.toIntValue()){
			sql.append("count(*) value from `orders` where 1=1");
		}
		if(status==TransactionAnalysisStatus.PAYMONEY.toIntValue()){
			sql.append("sum(pay_money) value from `transaction` where 1=1");
		}
		if(status==TransactionAnalysisStatus.ORDERSUSERCOUNT.toIntValue()){
			sql.append("count(distinct user_id) value from `orders` where 1=1");
		}
		if(status==TransactionAnalysisStatus.ORDERSMONEY.toIntValue()){
			sql.append("sum(pay_money) value from `orders` where 1=1");
		}
		if(status==TransactionAnalysisStatus.PAYUSERCOUNT.toIntValue()){
			sql.append("count(distinct user_id) value from `transaction` where 1=1");
		}
		if(status==TransactionAnalysisStatus.PAYORDERSCOUNT.toIntValue()){
			sql.append("count(*) value from `transaction` where 1=1");
		}
		
		if(StringUtils.isNotBlank(shopId)){
			sql.append(" and shop_id = ?");
			param.setString(shopId);
		}
		
		sql.append(" group by date");
		sql.append(" having date <= date_format(? ,?)");
		sql.append(" and date >= date_format(? ,?)");
		param.setDate(endTime);
		param.setString("%Y-%m-%d");
		param.setDate(startTime);
		param.setString("%Y-%m-%d");

		return queryForList(sql.toString(), AnalysisBO.class, param);
	}


	@Override
	public List<TransactionAnalysisBO> findAll(String shopId,Integer days) {
		StringBuffer sql = new StringBuffer();
		StatementParameter param = new StatementParameter();
		
		sql.append("select t.date,o.orders_user_count,o.orders_count,o.orders_money,o.orders_goods_count,t.pay_user_count,t.pay_orders_count,");
		sql.append("t.pay_money,t.pay_number from");
		sql.append(" (select date_format(os.created_time ,?) date,count(distinct os.user_id) 'orders_user_count',");
		param.setString("%Y-%m-%d");
		
		sql.append("count(os.id) 'orders_count',sum(os.pay_money) 'orders_money',sum(og.number) 'orders_goods_count' from `orders` os inner join orders_goods og on");
		sql.append(" os.id=og.order_id where os.is_delete=?");
		param.setInt(IsDelete.NO.toIntValue());
		
		if(StringUtils.isNotBlank(shopId)){
			sql.append(" and os.shop_id = ?");
			param.setString(shopId);
		}
		
		sql.append(" group by date having date <= date_sub(curdate(), interval 1 day) and date >= date_sub(curdate(), interval ? day)");
		param.setInt(days);
		
		sql.append(") o right join (select date_format(created_time ,?) date,count(distinct user_id) 'pay_user_count',");
		param.setString("%Y-%m-%d");
		
		sql.append("count(*) 'pay_orders_count',sum(pay_money) 'pay_money',sum(number) 'pay_number'");
		sql.append(" from `transaction`");
		
		if(StringUtils.isNotBlank(shopId)){
			sql.append(" where shop_id = ?");
			param.setString(shopId);
		}
		
		sql.append(" group by date having date <= date_sub(curdate(), interval 1 day) and date >= date_sub(curdate(), interval ? day)");
		param.setInt(days);
		
		sql.append(") t on o.date=t.date");
		sql.append(" union");
		
		sql.append(" select o.date,o.orders_user_count,o.orders_count,o.orders_money,o.orders_goods_count,t.pay_user_count,t.pay_orders_count,");
		sql.append("t.pay_money,t.pay_number from");
		sql.append(" (select date_format(os.created_time ,?) date,count(distinct os.user_id) 'orders_user_count',");
		param.setString("%Y-%m-%d");
		
		sql.append("count(os.id) 'orders_count',sum(os.pay_money) 'orders_money',sum(og.number) 'orders_goods_count' from `orders` os inner join orders_goods og on os.id=og.order_id where os.is_delete=?");
		param.setInt(IsDelete.NO.toIntValue());
		
		if(StringUtils.isNotBlank(shopId)){
			sql.append(" and os.shop_id = ?");
			param.setString(shopId);
		}
		
		sql.append(" group by date having date <= date_sub(curdate(), interval 1 day) and date >= date_sub(curdate(), interval ? day)");
		param.setInt(days);
		
		sql.append(") o left join (select date_format(created_time ,?) date,count(distinct user_id) 'pay_user_count',");
		param.setString("%Y-%m-%d");
		
		sql.append("count(*) 'pay_orders_count',sum(pay_money) 'pay_money',sum(number) 'pay_number'");
		sql.append(" from `transaction`");
		
		if(StringUtils.isNotBlank(shopId)){
			sql.append(" where shop_id = ?");
			param.setString(shopId);
		}
		
		sql.append(" group by date having date <= date_sub(curdate(), interval 1 day) and date >= date_sub(curdate(), interval ? day)");
		param.setInt(days);
		
		sql.append(") t on o.date=t.date");
		
		return queryForList(sql.toString(), TransactionAnalysisBO.class, param);
	}

	@Override
	public List<AnalysisBO> findChainOrdersTransaction(HomeTableBO homeTableBO) {
		StringBuffer sql = new StringBuffer();
		StatementParameter param = new StatementParameter();
		sql.append("select day(o.created_time) date,");
		if(homeTableBO.getStatus() == TransactionAnalysisStatus.ORDERSCOUNT.toIntValue()){
			sql.append("count(o.id) value");
		}
		if(homeTableBO.getStatus() == TransactionAnalysisStatus.ORDERSUSERCOUNT.toIntValue()){
			sql.append("count(distinct o.user_id) value");
		}
		if(homeTableBO.getStatus() == TransactionAnalysisStatus.ORDERSMONEY.toIntValue()){
			sql.append("sum(o.pay_money) value");
		}
		sql.append(" from orders o inner join shop s on o.shop_id = s.id");
		sql.append(" inner join chain c on s.chain_id = c.id");
		sql.append(" where c.id = ? and o.is_delete = ?");
		param.setString(homeTableBO.getChainId());
		param.setInt(Is.NO.toIntValue());
		if(homeTableBO.getShopId() != null){
			sql.append(" and o.shop_id = ?");
			param.setString(homeTableBO.getShopId());
		}
		sql.append(" and month(o.created_time)=?");
		sql.append(" and year(o.created_time)=?");
		param.setInt(homeTableBO.getMonth());
		param.setInt(homeTableBO.getYear());
		sql.append(" group by day(o.created_time) ");
		
		return queryForList(sql.toString(), AnalysisBO.class, param);
	}

	@Override
	public List<AnalysisBO> findChainPayTransaction(HomeTableBO homeTableBO) {
		StringBuffer sql = new StringBuffer();
		StatementParameter param = new StatementParameter();
		sql.append("select day(ph.created_time) date,");
		if(homeTableBO.getStatus() == TransactionAnalysisStatus.PAYORDERSCOUNT.toIntValue()){
			sql.append("count(ph.id) value");
		}
		if(homeTableBO.getStatus() == TransactionAnalysisStatus.PAYUSERCOUNT.toIntValue()){
			sql.append("count(distinct ph.user_id) value");
		}
		if(homeTableBO.getStatus() == TransactionAnalysisStatus.PAYMONEY.toIntValue()){
			sql.append("sum(ph.money) value");
		}
		sql.append(" from pay_history ph inner join shop s on ph.shop_id = s.id");
		sql.append(" inner join chain c on s.chain_id = c.id");
		sql.append(" where c.id = ? and ph.is_delete = ?");
		param.setString(homeTableBO.getChainId());
		param.setInt(Is.NO.toIntValue());
		if(homeTableBO.getShopId() != null){
			sql.append(" and ph.shop_id = ?");
			param.setString(homeTableBO.getShopId());
		}
		sql.append(" and month(ph.created_time)=?");
		sql.append(" and year(ph.created_time)=?");
		param.setInt(homeTableBO.getMonth());
		param.setInt(homeTableBO.getYear());
		sql.append(" group by day(ph.created_time) ");
		
		
		return queryForList(sql.toString(), AnalysisBO.class, param);
	}

	@Override
	public TransactionAnalysisBO getChainTransactionAnalysis(String shopId, Date startTime, Date endTime,String chainId,String date,Integer analysisType) {
		StringBuffer sql=new StringBuffer();
		StatementParameter params=new StatementParameter();
		sql.append("select");
		sql.append(" oc.orders_user_count,oc.orders_count,oc.orders_goods_count,oc.orders_money,tc.pay_user_count,tc.pay_orders_count,tc.pay_money,tc.pay_number,oc.orders_user_count/shc.uv orders_conversion,tc.pay_user_count/shc.uv pay_conversion,tc.pay_user_count/oc.orders_user_count pay_success,tc.client_price");
		if(StringUtils.isNoneBlank(shopId)){
			if(analysisType==AnalysisType.DAY.toIntValue()){
				sql.append(",(select count(t.id) c from transaction t where t.is_delete=? and date_format(t.created_time,'%Y-%m-%d') between date_sub(?,interval 7 day) and  ? and t.shop_id=? group by t.user_id having c>=2) seven_repeat_purchase,");
				sql.append("(select count(t.id) c from transaction t where t.is_delete=? and date_format(t.created_time,'%Y-%m-%d') between date_sub(?,interval 30 day) and ? and t.shop_id=? group by t.user_id having c>=2) thirty_repeat_purchase,");
				sql.append("(select count(t.id) c from transaction t where t.is_delete=? and date_format(t.created_time,'%Y-%m-%d') between date_sub(?,interval 90 day) and ? and t.shop_id=? group by t.user_id having c>=2) ninety_repeat_purchase");
				params.setInt(IsDelete.NO.toIntValue());
				params.setString(date);
				params.setString(date);
				params.setString(shopId);
				params.setInt(IsDelete.NO.toIntValue());
				params.setString(date);
				params.setString(date);
				params.setString(shopId);
				params.setInt(IsDelete.NO.toIntValue());
				params.setString(date);
				params.setString(date);
				params.setString(shopId);
			}
			if(analysisType==AnalysisType.DAY.toIntValue()||analysisType==AnalysisType.MONTH.toIntValue()){
				sql.append(" from");
				sql.append(" (select count(distinct o.user_id) orders_user_count,count(o.id) orders_count,sum(og.number) orders_goods_count,sum(o.pay_money) orders_money from orders o inner join orders_goods og on o.id=og.order_id where o.is_delete=? and o.shop_id=? and o.created_time like ?) oc,");
				sql.append("(select count(distinct t.user_id) pay_user_count,count(t.id) pay_orders_count,sum(t.pay_money) pay_money,sum(t.number) pay_number,sum(t.pay_money)/count(distinct t.user_id) client_price from transaction t where t.is_delete=? and t.shop_id=? and t.created_time like ?) tc,");
				sql.append("(select count(distinct sh.user_id) uv from shop_history sh where sh.is_delete=? and sh.shop_id=? and sh.type=? and sh.created_time like ?) shc");
				params.setInt(IsDelete.NO.toIntValue());
				params.setString(shopId);
				params.setString("%"+date+"%");
				params.setInt(IsDelete.NO.toIntValue());
				params.setString(shopId);
				params.setString("%"+date+"%");
				params.setInt(IsDelete.NO.toIntValue());
				params.setString(shopId);
				params.setInt(ShopHistoryType.VIEW.toIntValue());
				params.setString("%"+date+"%");
			}
			if(analysisType==AnalysisType.CUSTOM.toIntValue()){
				sql.append(" from");
				sql.append(" (select count(distinct o.user_id) orders_user_count,count(o.id) orders_count,sum(og.number) orders_goods_count,sum(o.pay_money) orders_money from orders o inner join orders_goods og on o.id=og.order_id where o.is_delete=? and o.shop_id=? and o.created_time between ? and ?) oc,");
				sql.append("(select count(distinct t.user_id) pay_user_count,count(t.id) pay_orders_count,sum(t.pay_money) pay_money,sum(t.number) pay_number,sum(t.pay_money)/count(distinct t.user_id) client_price from transaction t where t.is_delete=? and t.shop_id=? and t.created_time between ? and ?) tc,");
				sql.append("(select count(distinct sh.user_id) uv from shop_history sh where sh.is_delete=? and sh.shop_id=? and sh.type=? and sh.created_time between ? and ?) shc");
				params.setInt(IsDelete.NO.toIntValue());
				params.setString(shopId);
				params.setDate(startTime);
				params.setDate(endTime);
				params.setInt(IsDelete.NO.toIntValue());
				params.setString(shopId);
				params.setDate(startTime);
				params.setDate(endTime);
				params.setInt(IsDelete.NO.toIntValue());
				params.setString(shopId);
				params.setInt(ShopHistoryType.VIEW.toIntValue());
				params.setDate(startTime);
				params.setDate(endTime);
			}
		}else{
			if(analysisType==AnalysisType.DAY.toIntValue()){
				sql.append(",(select count(t.id) c from transaction t where t.is_delete=? and date_format(t.created_time,'%Y-%m-%d') between date_sub(?,interval 7 day) and  ? and t.shop_id=? group by t.user_id having c>=2) seven_repeat_purchase,");
				sql.append("(select count(t.id) c from transaction t where t.is_delete=? and date_format(t.created_time,'%Y-%m-%d') between date_sub(?,interval 30 day) and ? and t.shop_id in (select s.id from shop s where s.chain_id=?) group by t.user_id having c>=2) thirty_repeat_purchase,");
				sql.append("(select count(t.id) c from transaction t where t.is_delete=? and date_format(t.created_time,'%Y-%m-%d') between date_sub(?,interval 90 day) and ? and t.shop_id in (select s.id from shop s where s.chain_id=?) group by t.user_id having c>=2) ninety_repeat_purchase");
				params.setInt(IsDelete.NO.toIntValue());
				params.setString(date);
				params.setString(date);
				params.setString(chainId);
				params.setInt(IsDelete.NO.toIntValue());
				params.setString(date);
				params.setString(date);
				params.setString(chainId);
				params.setInt(IsDelete.NO.toIntValue());
				params.setString(date);
				params.setString(date);
				params.setString(chainId);
			}
			if(analysisType==AnalysisType.DAY.toIntValue()||analysisType==AnalysisType.MONTH.toIntValue()){
				sql.append(" from");
				sql.append(" (select count(distinct o.user_id) orders_user_count,count(o.id) orders_count,sum(og.number) orders_goods_count,sum(o.pay_money) orders_money from orders o inner join orders_goods og on o.id=og.order_id where o.is_delete=? and o.shop_id in (select s.id from shop s where s.chain_id=?) and o.created_time like ?) oc,");
				sql.append("(select count(distinct t.user_id) pay_user_count,count(t.id) pay_orders_count,sum(t.pay_money) pay_money,sum(t.number) pay_number,sum(t.pay_money)/count(distinct t.user_id) client_price from transaction t where t.is_delete=? and t.shop_id in (select s.id from shop s where s.chain_id=?) and t.created_time like ?) tc,");
				sql.append("(select count(distinct sh.user_id) uv from shop_history sh where sh.is_delete=? and sh.shop_id in (select s.id from shop s where s.chain_id=?) and sh.type=? and sh.created_time like ?) shc");
				params.setInt(IsDelete.NO.toIntValue());
				params.setString(chainId);
				params.setString("%"+date+"%");
				params.setInt(IsDelete.NO.toIntValue());
				params.setString(chainId);
				params.setString("%"+date+"%");
				params.setInt(IsDelete.NO.toIntValue());
				params.setString(chainId);
				params.setInt(ShopHistoryType.VIEW.toIntValue());
				params.setString("%"+date+"%");
			}
			if(analysisType==AnalysisType.CUSTOM.toIntValue()){
				sql.append(" from");
				sql.append(" (select count(distinct o.user_id) orders_user_count,count(o.id) orders_count,sum(og.number) orders_goods_count,sum(o.pay_money) orders_money from orders o inner join orders_goods og on o.id=og.order_id where o.is_delete=? and o.shop_id in (select s.id from shop s where s.chain_id=?) and o.created_time between ? and ?) oc,");
				sql.append("(select count(distinct t.user_id) pay_user_count,count(t.id) pay_orders_count,sum(t.pay_money) pay_money,sum(t.number) pay_number,sum(t.pay_money)/count(distinct t.user_id) client_price from transaction t where t.is_delete=? and t.shop_id in (select s.id from shop s where s.chain_id=?) and t.created_time between ? and ?) tc,");
				sql.append("(select count(distinct sh.user_id) uv from shop_history sh where sh.is_delete=? and sh.shop_id in (select s.id from shop s where s.chain_id=?) and sh.type=? and sh.created_time between ? and ?) shc");
				params.setInt(IsDelete.NO.toIntValue());
				params.setString(chainId);
				params.setDate(startTime);
				params.setDate(endTime);
				params.setInt(IsDelete.NO.toIntValue());
				params.setString(chainId);
				params.setDate(startTime);
				params.setDate(endTime);
				params.setInt(IsDelete.NO.toIntValue());
				params.setString(chainId);
				params.setInt(ShopHistoryType.VIEW.toIntValue());
				params.setDate(startTime);
				params.setDate(endTime);
			}
		}
		return query(sql.toString(), TransactionAnalysisBO.class, params);
	}

	@Override
	public List<AnalysisBO> findAllChainTransactionAnalysis(String shopId, Integer status, Integer analysisType,String date,String chainId,Date startTime,Date endTime) {
		StringBuffer sql=new StringBuffer();
		StatementParameter params=new StatementParameter();
		sql.append("select");
		sql.append(" date_format(created_time,?) date,");
		if(status==TransactionAnalysisStatus.ORDERSCOUNT.toIntValue()){
			sql.append("count(id) value from orders");
		}
		if(status==TransactionAnalysisStatus.PAYMONEY.toIntValue()){
			sql.append("sum(pay_money) value from transaction");
		}
		if(status==TransactionAnalysisStatus.ORDERSUSERCOUNT.toIntValue()){
			sql.append("count(distinct user_id) value from orders");
		}
		if(status==TransactionAnalysisStatus.ORDERSMONEY.toIntValue()){
			sql.append("sum(pay_money) value from orders");
		}
		if(status==TransactionAnalysisStatus.PAYUSERCOUNT.toIntValue()){
			sql.append("count(distinct user_id) value from transaction");
		}
		if(status==TransactionAnalysisStatus.PAYORDERSCOUNT.toIntValue()){
			sql.append("count(id) value from transaction");
		}
		if(analysisType==AnalysisType.DAY.toIntValue()||analysisType==AnalysisType.CUSTOM.toIntValue()){
			params.setString("%Y-%m-%d");			
		}else{
			params.setString("%Y-%m");
		}
		if(StringUtils.isNoneBlank(shopId)){
			sql.append(" where shop_id=?");
			params.setString(shopId);
		}else{
			sql.append(" where shop_id in (select id from shop where chain_id=?)");
			params.setString(chainId);
		}
		sql.append(" group by date");
		if(analysisType==AnalysisType.DAY.toIntValue()){
			sql.append(" having date between date_sub(?,interval ? day) and ?");
			params.setString(date);
			params.setInt(29);
			params.setString(date);
		}
		if(analysisType==AnalysisType.MONTH.toIntValue()){
			sql.append(" having date between date_sub(?,interval ? month) and ?");
			params.setString(date);
			params.setInt(5);
			params.setString(date);
		}
		if(analysisType==AnalysisType.CUSTOM.toIntValue()){
			sql.append(" having date between ? and ?");
			params.setDate(startTime);
			params.setDate(endTime);
		}
		return queryForList(sql.toString(), AnalysisBO.class,params);
	}

	@Override
	public List<TransactionAnalysisBO> getChainTransactionAnalysisList(String shopId, String chainId,Integer days) {
		StringBuffer sql=new StringBuffer();
		StatementParameter params=new StatementParameter();
		sql.append("select");
		sql.append(" oc.date,oc.orders_user_count,oc.orders_count,oc.orders_goods_count,oc.orders_money,tc.pay_user_count,tc.pay_orders_count,tc.pay_money,tc.pay_number,oc.orders_user_count/shc.uv orders_conversion,tc.pay_user_count/shc.uv pay_conversion,tc.pay_user_count/oc.orders_user_count pay_success,tc.client_price");
		if(StringUtils.isNoneBlank(shopId)){
				sql.append(" from");
				sql.append(" (select date_format(o.created_time,'%Y-%m-%d') date,count(distinct o.user_id) orders_user_count,count(o.id) orders_count,sum(og.number) orders_goods_count,sum(o.pay_money) orders_money from orders o inner join orders_goods og on o.id=og.order_id where o.is_delete=? and o.shop_id=? and o.created_time between date_sub(curdate(),interval ? day) and curdate() group by date) oc");
				sql.append(" left join (select date_format(t.created_time,'%Y-%m-%d') date,count(distinct t.user_id) pay_user_count,count(t.id) pay_orders_count,sum(t.pay_money) pay_money,sum(t.number) pay_number,sum(t.pay_money)/count(distinct t.user_id) client_price from transaction t where t.is_delete=? and t.shop_id=? and t.created_time between date_sub(curdate(),interval ? day) and curdate() group by date) tc on oc.date=tc.date");
				sql.append(" left join (select date_format(sh.created_time,'%Y-%m-%d') date,count(distinct sh.user_id) uv from shop_history sh where sh.is_delete=? and sh.shop_id=? and sh.type=? and sh.created_time between date_sub(curdate(),interval ? day) and curdate() group by date) shc on tc.date=shc.date");
				params.setInt(IsDelete.NO.toIntValue());
				params.setString(shopId);
				params.setInt(days);
				params.setInt(IsDelete.NO.toIntValue());
				params.setString(shopId);
				params.setInt(days);
				params.setInt(IsDelete.NO.toIntValue());
				params.setString(shopId);
				params.setInt(ShopHistoryType.VIEW.toIntValue());
				params.setInt(days);
		}else{
				sql.append(" from");
				sql.append(" (select date_format(o.created_time,'%Y-%m-%d') date,count(distinct o.user_id) orders_user_count,count(o.id) orders_count,sum(og.number) orders_goods_count,sum(o.pay_money) orders_money from orders o inner join orders_goods og on o.id=og.order_id where o.is_delete=? and o.shop_id in (select s.id from shop s where s.chain_id=?) and o.created_time between date_sub(curdate(),interval ? day) and curdate() group by date) oc");
				sql.append(" left join (select date_format(t.created_time,'%Y-%m-%d') date,count(distinct t.user_id) pay_user_count,count(t.id) pay_orders_count,sum(t.pay_money) pay_money,sum(t.number) pay_number,sum(t.pay_money)/count(distinct t.user_id) client_price from transaction t where t.is_delete=? and t.shop_id in (select s.id from shop s where s.chain_id=?) and t.created_time between date_sub(curdate(),interval ? day) and curdate() group by date) tc on oc.date=tc.date");
				sql.append(" left join (select date_format(sh.created_time,'%Y-%m-%d') date,count(distinct sh.user_id) uv from shop_history sh where sh.is_delete=? and sh.shop_id in (select s.id from shop s where s.chain_id=?) and sh.type=? and sh.created_time between date_sub(curdate(),interval ? day) and curdate() group by date) shc on tc.date=shc.date");
				params.setInt(IsDelete.NO.toIntValue());
				params.setString(chainId);
				params.setInt(days);
				params.setInt(IsDelete.NO.toIntValue());
				params.setString(chainId);
				params.setInt(days);
				params.setInt(IsDelete.NO.toIntValue());
				params.setString(chainId);
				params.setInt(ShopHistoryType.VIEW.toIntValue());
				params.setInt(days);
		}
		return queryForList(sql.toString(), TransactionAnalysisBO.class, params);
	}
}
